﻿Public Class CKonsentrasi
    Inherits CContext
    Private INSERT_COMMAND As String
    Private UPDATE_COMMAND As String
    Private DELETE_COMMAND As String
    Private SELECT_COMMAND As String

    Public Sub New()
        MyBase.New()
    End Sub

    Public Function getAllByIdGuru(ByVal idGuru As Integer) As DataTable

        Me.SELECT_COMMAND = "select mp.id, mp.nama, k.id_guru, g.nama as nama_guru from tbl_mata_pelajaran mp left join (select * from tbl_konsentrasi tk where tk.id_guru = " & idGuru & ") k on mp.id = k.id_mp left join tbl_guru g on k.id_guru = g.id"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    ''' <summary>
    ''' Simpan perubahan data konsentrasi
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="DataKonsentrasi">id, nama, id_guru, nama_guru</param>
    ''' <remarks></remarks>
    Public Sub RubahTSql(ByVal idGuru As Integer, ByVal DataKonsentrasi As DataTable)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try

            'hapus data lama
            DELETE_COMMAND = "DELETE FROM TBL_KONSENTRASI WHERE ID_GURU=" & idGuru
            Dim MYSQL_DELETE_CMD As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With MYSQL_DELETE_CMD
                .CommandText = DELETE_COMMAND
                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            Dim MYSQL_INSERT_CMD As MySql.Data.MySqlClient.MySqlCommand
            For Each aRow As DataRow In DataKonsentrasi.Rows
                If Not IsDBNull(aRow.Item("id_guru")) Or aRow.Item("id_guru").ToString.Trim <> "" Then
                    MYSQL_INSERT_CMD = MyBase.MySqlConnect.CreateCommand
                    With MYSQL_INSERT_CMD
                        .CommandText = "INSERT INTO TBL_KONSENTRASI(ID_GURU,ID_MP) VALUES (" & aRow.Item("ID_GURU") & "," & aRow.Item("ID") & ")"
                        .Transaction = _TMysql
                        .ExecuteNonQuery()
                    End With
                End If
            Next

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try

    End Sub

End Class
